﻿using MySqlConnector;
using System.Data;

namespace ClassMySQL
{
    public static class ServerMySQL
    {
        // Поля для создания строки подключения
        private static MySqlConnectionStringBuilder db = new MySqlConnectionStringBuilder();
        private static MySqlConnection connection = new MySqlConnection();
        private static MySqlCommand cmd = new MySqlCommand();
        private static string sql = "";

        /// <summary>
        /// Получение названия базы данных
        /// </summary>
        public static string Database {  get { return connection.Database; } }

        // Создание строки подключения

        /// <summary>
        /// Укажите адрес сервера,
        /// название базы данных,
        /// логин и пароль от базы данных
        /// </summary>
        /// <param name="host"></param>
        /// <param name="dbName"></param>
        /// <param name="login"></param>
        /// <param name="pass"></param>
        public static void DB_Load(string host, string dbName, string login, string pass)
        {
            db = new MySqlConnectionStringBuilder
            {
                Server = host,
                Database = dbName,
                UserID = login,
                Password = pass,
                CharacterSet = "utf8"
            };
            connection = new MySqlConnection(db.ConnectionString);
        }

        /// <summary>
        /// Проверка соединения с БД
        /// </summary>
        /// <returns></returns>
        public static bool CheckConnect()
        {
            try
            {
                connection.Open();
                connection.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        /// Получение данных с сервера по названию таблицы
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string tableName)
        {
            DataTable dt = new DataTable();
            sql = $"SELECT * FROM {db.Database}.{tableName}";
            cmd = new MySqlCommand(sql, connection);
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception)
            {
                return dt;
            }
            return dt;
        }

        /// <summary>
        /// Получение данных по произвольному SQL запросу (SELECT)
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public static DataTable GetData(string sqlCommand)
        {
            DataTable dt = new DataTable();
            cmd = new MySqlCommand(sqlCommand, connection);
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception)
            {
                return dt;
            }
            return dt;
        }

        public static bool TestConnect_tmp(string host, string db, string login, string pass)
        {
            MySqlConnectionStringBuilder db_temp = new MySqlConnectionStringBuilder
            {
                Server = host,
                Database = db,
                UserID = login,
                Password = pass,
                CharacterSet = "utf8"
            };
            MySqlConnection connection = new MySqlConnection(db_temp.ConnectionString);
            try
            {
                connection.Open();
                connection.Close();
                return true;
            }
            catch (Exception)
            { return false; }
        }

        /// <summary>
        /// Выполнение операций CRUD (создания, чтения, 
        /// модификации и удаления)
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public static int CrudOperation(string sqlCommand)
        {
            cmd = new MySqlCommand(sqlCommand, connection);

            int result = 0;
            try
            {
                connection.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                
            }
            connection.Close();
            return result;
        }
    }
}
